﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using DBUtil;
using ZXCompany.Models.Entity;
namespace ZXCompany.Models.Dao
{
    public class ZX_CompanyPointDao
    {
        private Database db = new Database();
        private List<Entity.ZX_CompanyPoint> GetListFromDT(DataTable dt)
        {
            List<Entity.ZX_CompanyPoint> lexpert = new List<Entity.ZX_CompanyPoint>();
            foreach (DataRow dr in dt.Rows)
            {
                Entity.ZX_CompanyPoint expert = new Entity.ZX_CompanyPoint();
                expert.ID = Convert.ToInt32(dr["ID"]);
                expert.title = dr["title"].ToString();
                expert.articleDate = dr["articleDate"].ToString();
                expert.detail = dr["detail"].ToString();
                expert.updateTime = dr["updateTime"].ToString();
                expert.addUserID = Convert.ToInt32(dr["addUserID"]);
                expert.addTime = dr["addTime"].ToString();
                lexpert.Add(expert);
            }
            return lexpert==null ?new List<ZX_CompanyPoint>(): lexpert;
        }
        /// <summary>
        /// 获取公司观点
        /// </summary>
        /// <param name="sconn"></param>
        /// <returns></returns>
        public List<Entity.ZX_CompanyPoint> Select(SqlConnection sconn)
        {
            try
            {
                string sql = @"SELECT isnull([ID],0) as [ID]
                                      ,isnull([detail],'') as [detail]
                                      ,isnull([title],'') as [title]
                                      ,isnull([articleDate],'') as [articleDate]
                                      ,isnull([addUserID],0) as [addUserID]
                                      ,isnull([addTime],'') as [addTime]
                                      ,isnull([updateTime],'') as [updateTime]
                                  FROM [ZX_CompanyPoint] order by articleDate desc";
                DataTable dt = db.Squery(sql, sconn);
                return GetListFromDT(dt);
            }
            catch (Exception ex)
            {

                throw ex;
            }
        }
        /// <summary>
        /// 通过ID获取公司观点
        /// </summary>
        /// <param name="sconn"></param>
        /// <returns></returns>
        public List<Entity.ZX_CompanyPoint> SelectById(int etid, SqlConnection sconn)
        {
            try
            {
                string sql = @"SELECT isnull([ID],0) as [ID]
                            ,isnull([detail],'') as [detail]
                            ,isnull([articleDate],'') as [articleDate]
                            ,isnull([title],'') as [title]
                            ,isnull([addUserID],0) as [addUserID]
                            ,isnull([addTime],'') as [addTime]
                            ,isnull([updateTime],'') as [updateTime]
                        FROM [ZX_CompanyPoint] where id=@id";
                SqlParameter[] para = new SqlParameter[]{
                    new SqlParameter("@id",etid)
                };
                DataTable dt = db.Squery(sql, sconn, para);
                return GetListFromDT(dt);
            }
            catch (Exception ex)
            {

                throw ex;
            }
        }

        /// <summary>
        /// 通过ID获取公司观点
        /// </summary>
        /// <param name="sconn"></param>
        /// <returns></returns>
        public int SelectMaxId(SqlConnection sconn)
        {
            try
            {
                string sql = @"SELECT isnull(max(title),0) + 1 as title FROM [ZX_CompanyPoint]";
                DataTable dt = db.Squery(sql, sconn);
                return Convert.ToInt32(dt.Rows[0][0]);
            }
            catch (Exception ex)
            {

                throw ex;
            }
        }

        /// <summary>
        /// 通过ID删除公司观点
        /// </summary>
        /// <param name="sconn"></param>
        /// <returns></returns>
        public void Delete(int etid, SqlConnection sconn, SqlTransaction stran)
        {
            try
            {
                string sql = @"delete FROM [ZX_CompanyPoint] where id=@id";
                SqlParameter[] para = new SqlParameter[]{
                    new SqlParameter("@id",etid)
                };
                DataTable dt = db.Squery(sql, sconn, stran, para);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 通过ID更新公司观点
        /// </summary>
        /// <param name="sconn"></param>
        /// <returns></returns>
        public void Update(Entity.ZX_CompanyPoint Entype, SqlConnection sconn, SqlTransaction stran)
        {
            try
            {
                string sql = @"Update ZX_CompanyPoint set
                            detail =@detail
                            ,articleDate=@articleDate
                            ,title=@title
                            ,addUserID=@addUserID
                            ,addTime=@addTime
                            ,updateTime=@updateTime
                            where id=@id";
                SqlParameter[] para = new SqlParameter[]{
                    new SqlParameter("@id",Entype.ID),
                    new SqlParameter("@detail",Entype.detail),
                    new SqlParameter("@articleDate",Entype.articleDate),
                    new SqlParameter("@title",Entype.title),
                    new SqlParameter("@addUserID",Entype.addUserID),
                    new SqlParameter("@addTime",Entype.addTime),
                    new SqlParameter("@updateTime",Entype.updateTime)
                };
                DataTable dt = db.Squery(sql, sconn,stran, para);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 通过ID更新公司观点
        /// </summary>
        /// <param name="sconn"></param>
        /// <returns></returns>
        public void Insert(Entity.ZX_CompanyPoint Entype, SqlConnection sconn, SqlTransaction stran)
        {
            try
            {
                string sql = @"Insert into ZX_CompanyPoint (detail,title,articleDate,addUserID,addTime) values
                             (@detail,@title,@articleDate,@addUserID,@addTime)";
                SqlParameter[] para = new SqlParameter[]{
                    new SqlParameter("@detail",Entype.detail),
                    new SqlParameter("@articleDate",Entype.articleDate),
                    new SqlParameter("@title",Entype.title),
                    new SqlParameter("@addUserID",Entype.addUserID),
                    new SqlParameter("@addTime",Entype.addTime)
                };
                db.Udaquery(sql, sconn, stran, para);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
    }
}